﻿
CREATE PROCEDURE [dbo].[balcon_GenRegistruCasaArhiva]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@Cod varchar(50),
	@DataDocJust bit,
	@an smallint
AS
BEGIN

	SET NOCOUNT ON;
	

	SELECT ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc,
		  ct.DebitInitial - ct.CreditInitial + 
		(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM arhRulaje rlj JOIN arhRulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContDebit = ct.Cont) AND rjd.DataDoc<rd.DataDoc  and ct.An=rjd.An)
		-(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM arhRulaje rlj JOIN arhRulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContCredit = ct.Cont) AND rjd.DataDoc<rd.DataDoc and ct.An=rjd.An) 
			sodlzi,
		SUM(CASE WHEN ruld.ContDebit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajDebit,
		SUM(CASE WHEN ruld.ContCredit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajCredit
		FROM [arhConturi] ct 
		LEFT OUTER JOIN [ConturiSpeciale] cs ON ct.Cont = cs.Cont AND ct.CodUnitate = cs.CodUnitate 
		JOIN [arhRulaje] ruld ON ct.CodUnitate=ruld.CodUnitate AND (ruld.ContDebit = ct.Cont OR ruld.ContCredit=ct.Cont) and ct.An=ruld.An
		JOIN [arhRulajeDoc] rd ON ruld.IDDocument = rd.IDDocument and ct.An=rd.An
		WHERE 
		ct.An=@An AND 
		ct.CodUnitate = @CodUnitate AND rd.DataDoc>=@deladata AND rd.DataDoc<=@PanaLaData
		AND (LEN(@Cod)=0 OR cs.Tipuri LIKE @Cod)
		GROUP BY ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc,ct.Cont,ct.An
	
		
END